﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using WaterBill.DTO;

namespace WaterBill.DAL
{
    class DAL_Bills : clsConnection
    {
        DataTable dtB;
        //Lay danh sach hoa don
        public DataTable DAL_Bills_GetAll()
        {
            dtB = new DataTable();
            try
            {
                dtB = ExcuteQuery("GetAllBill");
            }
            catch
            {
            }
            return dtB;
        }
        //Lay danh sach hoa don theo khach hang
        public DataTable DAL_Bills_GetByCustomerId(int CustomerId)
        {
            dtB = new DataTable();
            try
            {
                AddParameter(new SqlParameter("@CustomerId", CustomerId));
                dtB = ExcuteQuery("GetBillByCustomerId");
            }
            catch
            {
            }
            return dtB;
        }

        //Lay danh sach hoa don theo thang su dung
        public DataTable DAL_Bills_GetByChargeId(int ChargeId)
        {
            dtB = new DataTable();
            try
            {
                AddParameter(new SqlParameter("@ChargeId", ChargeId));
                dtB = ExcuteQuery("GetBillByChargeId");
            }
            catch
            {
            }
            return dtB;
        }

        //Lay danh sach hoa don theo khach hang va thang su dung
        public DataTable DAL_Bills_GetByCustomerIdAndChargeId(int CustomerId, int ChargeId)
        {
            dtB = new DataTable();
            try
            {
                AddParameter(new SqlParameter("@CustomerId", CustomerId));
                AddParameter(new SqlParameter("@ChargeId", ChargeId));
                dtB = ExcuteQuery("GetBillByCustomerIdAndChargeId");
            }
            catch
            {
            }
            return dtB;
        }

        //Lay danh sach hoa don co chi so moi rong va khac thang su dung truyen vao
        public DataTable DAL_Bills_GetNewIndexNullAndOtherChargeMax()
        {
            dtB = new DataTable();
            try
            {
                dtB = ExcuteQuery("GetBillNewIndexNullAndOtherChargeMax");
            }
            catch
            {
            }
            return dtB;
        }

        //Lay danh sach hoa don co chi so moi rong va thang su dung la lon nho
        public DataTable DAL_Bills_GetNewIndexNull()
        {
            dtB = new DataTable();
            try
            {
                dtB = ExcuteQuery("GetBillNewIndexNull");
            }
            catch
            {
            }
            return dtB;
        }

        //Them mot hoa don mot
        public Boolean DAL_Bills_Insert(DTO_Bills obj)
        {
            try
            {
                AddParameter(new SqlParameter("@CustomerId", obj.CustomerId));
                AddParameter(new SqlParameter("@ChargeId", obj.ChargeId));
                AddParameter(new SqlParameter("@Status", 1));
                if (ExecuteScalar("InsertBill") == 1)
                {
                    return true;
                }
            }
            catch
            {
            }
            return false;
        }

        //Sua hoa don
        public Boolean DAL_Bills_Update(DTO_Bills obj)
        {
            try
            {
                AddParameter(new SqlParameter("@BillId", obj.BillId));
                AddParameter(new SqlParameter("@NewIndex", obj.NewIndex));
                AddParameter(new SqlParameter("@UserId", obj.UserId));
                if (ExecuteScalar("UpdateBill") == 1)
                {
                    return true;
                }
            }
            catch
            {
            }
            return false;
        }

        //Sua hoa don dua vao billnumber
        public Boolean DAL_Bills_UpdateByBillNumber(DTO_Bills obj)
        {
            try
            {
                AddParameter(new SqlParameter("@BillNumber", obj.BillNumber.Trim()));
                AddParameter(new SqlParameter("@NewIndex", obj.NewIndex));
                AddParameter(new SqlParameter("@UserId", obj.UserId));
                if (ExecuteScalar("UpdateBillByBillNumber") == 1)
                {
                    return true;
                }
            }
            catch
            {
            }
            return false;
        }

        //Cap nhat trang thai cua hoa don
        public Boolean DAL_Bills_UpdateStatus(DTO_Bills obj)
        {
            try
            {
                AddParameter(new SqlParameter("@BillId", obj.BillId));
                AddParameter(new SqlParameter("@Status", obj.Status));
                if (ExcuteNonQuery("UpdateStatusBill"))
                {
                    return true;
                }
            }
            catch
            {
            }
            return false;
        }

        //Lay Tong Danh Thu
        public DataTable DAL_Bills_GetTotalPrice()
        {
            dtB = new DataTable();
            try
            {
                dtB = ExcuteQuery("GetBillTotalPrice");
            }
            catch
            {
            }
            return dtB;
        }

        //Lay Tong Danh Thu theo nam
        public DataTable DAL_Bills_GetYearStats(int start, int end)
        {
            dtB = new DataTable();
            try
            {
                AddParameter(new SqlParameter("@start", start));
                AddParameter(new SqlParameter("@end", end));
                dtB = ExcuteQuery("GetBillYearStats");
            }
            catch
            {
            }
            return dtB;
        }

        //Lay Tong Danh Thu theo thang
        public DataTable DAL_Bills_GetMonthStats(int start, int end, int year)
        {
            dtB = new DataTable();
            try
            {
                AddParameter(new SqlParameter("@start", start));
                AddParameter(new SqlParameter("@end", end));
                AddParameter(new SqlParameter("@year", year));
                dtB = ExcuteQuery("GetBillMonthStats");
            }
            catch
            {
            }
            return dtB;
        }

        //Dem so hoa don dua vao khach hang
        public int DAL_Bills_CountByCustomerId(int CustomerId)
        {
            int count = 0;
            dtB = new DataTable();
            dtB = ExcuteQuery("GetBillCountByCustomerId");
            count = dtB.Rows.Count;
            return count;
        }
    }
}
